﻿using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace TeachingAffairsManagementSystem
{
    public class DbHelper:IDisposable
    {
        
        private readonly MySqlConnection _connection = new MySqlConnection
        {
            ConnectionString = "Server=localhost;Database=sitedb;Uid=root;Pwd=123456;"
        };

        public DbHelper()
        {
            _connection.Open();
        }
        
        public void Dispose()
        {
            _connection.Dispose();
        }
        
        /// <summary>
        /// 执行无查询语句 返回影响行数 用于插入、修改、删除
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlString)
        {
            
            MySqlCommand command = new MySqlCommand
            {
                Connection = _connection,
                CommandType = CommandType.Text,
                CommandText = sqlString
            };
            int ret = command.ExecuteNonQuery();
            
            return ret;
        }

        /// <summary>
        /// 执行一条命令 返回第一个结果 用于执行函数
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sqlString)
        {
            
            MySqlCommand command = new MySqlCommand
            {
                Connection = _connection,
                CommandType = CommandType.Text,
                CommandText = sqlString
            };
            Object obj = command.ExecuteScalar();
             
            return obj;
        }

        /// <summary>
        /// 执行查询语句 返回查询结果
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public DataSet ExecuteQuery(string sqlString)
        {
            
            MySqlDataAdapter adapter = new MySqlDataAdapter(sqlString,_connection);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            
            return dataSet;
        }

        
    }
}